<?php

class Application_Model_Odbc {

    public function obrir_bd() {
        $dsn = 'dsn_pract_uf3';
        $username = 'root';
        $password = '';
        try {
            $link = odbc_connect($dsn, $username, $password);
            return $link;
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }

    public function tancar_bd($link) {
        odbc_close($link);
    }

    /*     * ******************************* DEPARTAMENTS *********************************** */

    public function getDept($id_dept) {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom,descripcio FROM dept WHERE id=?";
        $pstmt = odbc_prepare($link, $sql);
        $params = array($id_dept);
        $res = odbc_execute($pstmt, $params);
        $dept = odbc_fetch_array($pstmt);
        return $dept;
    }

    public function addDept($n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO dept (nom,descripcio) VALUES (?,?)";
        $pstmt = odbc_prepare($link, $sql);
        $params = array($n_dept, $d_dept);
        odbc_execute($pstmt, $params);
        $this->tancar_bd($link);
    }

    public function editDept($id_dept, $n_dept, $d_dept) {
        $link = $this->obrir_bd();
        $sql = "UPDATE dept SET nom = ?, descripcio = ? WHERE id=?";
        $pstmt = odbc_prepare($link, $sql);
        $params = array($n_dept, $d_dept, $id_dept);
        $res = odbc_execute($pstmt, $params);
        $this->tancar_bd($link);
    }

    public function deleteDept($id_dept) {
        $link = $this->obrir_bd();
        try {
            odbc_autocommit($link, FALSE);
            //esborrar empleats d'aquell dept
            $sql = "DELETE FROM emp WHERE id_dept=?";
            $pstmt = odbc_prepare($link, $sql);
            $params = array($id_dept);
            $res = odbc_execute($pstmt, $params);

            //esborrar dept
            $sql = "DELETE FROM dept WHERE id=?";
            $pstmt = odbc_prepare($link, $sql);
            $params = array($id_dept);
            $res = odbc_execute($pstmt, $params);
            odbc_commit($link);
        } catch (Exception $e) {
            odbc_rollback($link);
        }
        $this->tancar_bd($link);
    }

    public function listDepts() {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom,descripcio FROM dept";
        $i = 0;
        $depts = array();
        $rs = odbc_exec($link, $sql);
        while (odbc_fetch_row($rs)) {
            $id = odbc_result($rs, "id");
            $nom = odbc_result($rs, "nom");
            $desc = odbc_result($rs, "descripcio");
            $depts[$i] = array('id' => $id, 'nom' => $nom, 'descripcio' => $desc);
            $i++;
        }
        $this->tancar_bd($link);
        return $depts;
    }

    public function listDeptsSelect() {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom FROM dept";
        $depts = array();
        $rs = odbc_exec($link, $sql);
        while (odbc_fetch_row($rs)) {
            $id = odbc_result($rs, "id");
            $nom = odbc_result($rs, "nom");
            $depts[$id] = $nom;
        }
        $this->tancar_bd($link);
        return $depts;
    }

    /*     * ******************************* EMPS *********************************** */

    public function getEmp($id_emp) {
        $link = $this->obrir_bd();
        $sql = "SELECT id,nom,cognom,dni,id_dept as departament FROM emp where id=?";
        $pstmt = odbc_prepare($link, $sql);
        $params = array($id_emp);
        $res = odbc_execute($pstmt, $params);
        $emp = odbc_fetch_array($pstmt);
        return $emp;
    }

    public function addEmp($nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "INSERT INTO emp (nom,cognom,dni,id_dept) VALUES (?,?,?,?)";
        $pstmt = odbc_prepare($link, $sql);
        $params = array($nom_e, $cognom_e, $dni_e, $id_d);
        odbc_execute($pstmt, $params);
        $this->tancar_bd($link);
    }

    public function editEmp($id_e, $nom_e, $cognom_e, $dni_e, $id_d) {
        $link = $this->obrir_bd();
        $sql = "UPDATE emp SET nom = ? , cognom = ?,dni=?,id_dept=? WHERE id=?";
        $pstmt = odbc_prepare($link, $sql);
        $params = array($nom_e, $cognom_e, $dni_e, $id_d, $id_e);
        $res = odbc_execute($pstmt, $params);
        $this->tancar_bd($link);
    }

    public function deleteEmp($id_e) {
        $link = $this->obrir_bd();
        $sql = "DELETE FROM emp WHERE id=?";
        $pstmt = odbc_prepare($link, $sql);
        $params = array($id_e);
        $res = odbc_execute($pstmt, $params);
        $this->tancar_bd($link);
    }

    public function listEmps() {
        $link = $this->obrir_bd();
        $sql = "SELECT e.id,e.nom,e.cognom,e.dni,d.nom as departament FROM emp e, dept d WHERE e.id_dept=d.id";
        $i = 0;
        $emps = array();
        $rs = odbc_exec($link, $sql);
        while (odbc_fetch_row($rs)) {
            $id = odbc_result($rs, "id");
            $nom = odbc_result($rs, "nom");
            $cognom = odbc_result($rs, "cognom");
            $dni = odbc_result($rs, "dni");
            $dept = odbc_result($rs, "departament");
            $emps[$i] = array('id' => $id, 'nom' => $nom, 'cognom' => $cognom, 'dni' => $dni, 'departament' => $dept);
            $i++;
        }
        $this->tancar_bd($link);
        return $emps;
    }

}

